
package DBLayer;

import java.sql.*;

import java.util.ArrayList;
import modelLayer.*;

public class DbSalesOrder implements IFDBSalesOrder 
{
	private Connection con;
	
	//create a new instance of DBSalesOrder
	public DbSalesOrder() 
	{
		con = DbConnection.getInstance().getDBcon();
	}
	/*
	public ArrayList<SalesOrder> findSalesOrder(int salesId)
	{
		ArrayList<SalesOrder> saleObjects = new ArrayList<SalesOrder>();
		saleObjects = miscWhere("salesId = '" + salesId + "'", true);
		return saleObjects;
	}
	*/
	//find one order with salesId
	public SalesOrder findSalesId(int salesId, boolean retrieveAssociation)
	{
		String wClause = " salesId = '" + salesId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	
	//find an order from customers phoneNo
	public SalesOrder findCustomerPhoneNo(String customerPhoneNo, boolean retrieveAssociation)
		{
			String wCaluse = " phoneNo = '" + customerPhoneNo + "'";
			return singleWhere(wClause, retrieveAssociation);
		}
	/*	
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();
		
		String query = buildQuery(wClause);
		System.out.println("DbSalesOrder" + query);
		
		//read from SalesOrder
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		results = stmt.executeQuery(query);
		System.out.println("DbSalesOrder continued");
		
		while(results.next())
		{
			SalesOrder salesObj = new SalesOrder(0, query, 0, query, query, 0, 0, query, query);
			salesObj = buildSalesOrder(results);
			list.add(salesObj);
		}
		stmt.close();
		
		//for each salesorder find customername
		if(retrieveAssociation)
		{
			DBCustomer dbCustomer = new DBCustomer();
			for(SalesOrder sObj : list)
			{
				String phoneNo = sObj.getCustomerPhoneNo();
				Customer cObj = dbCustomer.findCustomer(phoneNo, retrieveAssociation);
				
				sObj.setCustomerPhoneNo(phoneNo);
			}
		}
		return list;
		
		//for each salesorder find productname
//		if(retrieveAssociation)
//		{
//			IFDBProduct dbProduct = new DbProduct();
//			for(SalesOrder sObj : list)
//			{
//				int productId = sObj.getProduct().getProductId();
//				Product pObj = dbProduct.findProduct(productId);
//				sObj.setProduct(pObj);
//			}
//		}
	}
	*/
	
	private SalesOrder buildSalesOrder(ResultSet results)
	{
		SalesOrder sObj = new SalesOrder(0, null, 0, null, null, 0, 0, null, null);
		Customer cObj = new Customer(null, null, null, null, null, null, null, null);
				
		{
			cObj.setPhoneNo(results.getString("phoneNo"));
			//pObj.setProductId(results.getInt("productId"));
			sObj.setDeliveryCosts(results.getDouble("deliveryCosts"));
			sObj.setDeliveryDate(results.getString("deliveryDate"));
			sObj.setDeliveryStatus(results.getString("deliveryStatus"));
			sObj.setDiscount(results.getDouble("discount"));
			sObj.setInvoiceNo(results.getString("invoiceNo"));
			sObj.setSalesDate(results.getString("salesDate"));
			sObj.setSalesId(results.getInt("salesId"));
			sObj.setTotalAmount(results.getdouble("totalamout"));
			
		}
		
		return sObj;
	}
	
	//insert new order
	public int insertSalesOrder(SalesOrder order)
	{
		//get the next sales id
		int nextSalesId = GetMax.getMaxId("select max(salesId) from salesorder");
		nextSalesId = newxtSalesId + 1;
		System.out.println("next salesId = " + nextSalesId);
		
		int rc = -1;
		String query = "INSERT INTO salesOrder(customerPhoneNo, deliveryCosts, deliveryDate, deliveryStatus, discount, invoiceNo, salesDate, salesId) VALUES('" + 
				order.getCustomer().getCustomerPhoneNo() + "', " + 
				order.getDeliveryCosts() + ", '" + 
				order.getDeliveryDate() + "', " +
				order.getDiscount() + ", '" + 
				order.getInvoiceNo() + "', '" + 
				order.getSalesDate() + "', " + 
				nextSalesId + ", " +
				order.getTotalAmount();
		
		System.out.println("Insert: " + query);
		
		try
		{
			//insert new order
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		
		catch(SQLException ex)
		{
			System.out.println("Order not created");
			throw new Exception("Order not inserted correctly");
		}
		return(rc);
	}
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT customerPhoneNo, deliveryCosts, deliveryDate, deliveryStatus, discount, invoiceNo, salesDate, salesId, totalAmount";
		
		if(wClause.length() > 0)
			query = query + "WHERE " + wClause;
		
		return query;
	}
	
	//method to select a single order
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder sObj = new SalesOrder(0, wClause, 0, wClause, wClause, 0, 0, wClause, wClause);
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try
		{
			//read the order from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				sObj = buildSalesOrder(results);
				stmt.close();
				
				if(retrieveAssociation)
				{
					//build customer phoneNo
					String customerPhoneNo = sObj.getCustomer().getCustomerPhoneNo();
					
					if(customerPhoneNo != null)
					{
						SalesOrder cPhone = singleWhere(" phoneNo = '" + customerPhoneNo + "'", false);
						sObj.setCustomerPhoneNo(cPhone);
						System.out.println("Customers phoneNo is selected");
					}
			}
				else
				{
					//no order was found
					sObj = null;
				}
			}
		}
			
		catch(Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return sObj;
	}

	@Override
	public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}
	
}

